Mysql中高级特性

---

mysql查询缓存

服务器根据缓存对相同的查询跳过解析和优化阶段，另一种缓存机制“查询缓存”，这种缓存保存了select 语句的完整结果集 ，查询缓存保留使用过的表，如果表发生了改变，那么缓存失效了，这种方式开销比较小，查询不会改变语义

• mysql检查缓存命中

  mysql 不会对语句解析、正则化或者参数化，只要字符大小写，空格或者注释一点不同，查询缓存就认为是一个不同的查询
  查询缓存不会缓存引用用户自定义函数，存储函数，用户自定义变量，临时表
  查询缓存值针对服务器第一次收到的完整的select，所以查询里的子查询或视图也不能缓存，存储过程中的查询不能使用缓存

  mysql查询缓存可以改善性能，开启查询缓存对于读写都增加了某些额外的开销
  读取查询之前必须检查缓存
  如果查询可以被缓存，但是不再缓存中，那么产生结果之后进行保存会带来一些额外的开销
  写入数据的查询也会有额外的开销，因为必须使缓存中相关的数据表失效

  对于Innodb引擎，事务会限制查询缓存的失效，长期运行的事务增加查询缓存未命中的数量，如果缓存中有许多查询，缓存失效就会需要很长的时间并且延缓整个系统的工作。查询缓存有一个全局所，检查查询是否命中，以及查询失效时候都会发生访问动作

• 缓存何时有帮助

  缓存不会自动比非缓存高效，缓存需要开销，当节省的资源大于开销，缓存才有真正的效率，和服务器负载有关
  缓存关闭时：读取操作执行查询语句返回给客户，写入操作需要执行查询
  缓存开启时：读取首先会检查查询缓存，要么执行返回结果，保存结果，再返回结果，每个写入操作需要执行查询语句

  并且检查是否缓存过的查询要失效

  查询结果可以减少产生结果的时间，不会减少结果发送到客户端的时间

• 缓存未命中的原因

  查询不可以缓存：包含不确定函数（current_date）,结果太大，无法缓存，状态qchache_not_cached 会因为这两种

  无法缓存的查询而增加
  服务器以前从来没有见过这个缓存
  查询的结果以前缓存过，服务器把它移除了

  服务器有很多缓存未命中，但是不能缓存的查询却很少
  查询缓存未激活，也就是服务器没有机会将结果存储到缓存中
  服务器看到未见过的查询
  有很多缓存失效

• 如何对查询缓存进行维护和调优

  query_cache_type
  表示缓存是否激活，（off on demand） demand意思是只有包含sql_cache 的选项才能被缓存，即是会话级 变量也是全局性变量
  query_cache_size
  分配给查询的总内存，以字节为单位。必须是1024的倍数
  query_cache_min_res_unit
  分配缓存块的最小值，
  query_cache_limit
  限制mysql存储的最大结果，如果查询结果比这个值大，那么就不会被缓存，如果超过限制，mysql 自动增加 qcache_not_cached 值，可以优先给查询加上 sql_no_cache 避免这个开销
  query_cache_wlock_invalidate
  是否换取其他联接已经锁定的表，默认是off，如果开启，阻止读取数据，有可能增加锁等待

---

Mysql中存储代码

mysql可以用触发器、存储过程和存储函数保存在服务器内部，为“事件”的周期性服务，存储过程和存储函数统称为“存储例程”
存储代码有优点：
它在代码存在的地方运行，所以可以在服务器内部运行服务，从而节约贷款和减少延迟
它是一种代码复用的方式，集中商业逻辑，增强程序行为的一致性
减少发布和维护的开销
提供安全性方面的优势，以及更好的控制权限的方式
服务器缓存存储过程执行计划，降低重复调用的开销
存储代码保存在服务器里面和数据库程序员工作可以分开

• 存储过程和函数

  存储过程杜宇某些类型操作还是很快的，尤其是对于小型查询，如果查询足够小，解析和网络通信的开销会成为主要因素，

     drop procedure if  exists insert_many_rows ;
     delimiter // 
     create procesuce insert_many_rows (in loops int)
     begin
     declare v1 int ;
     set v1 = loops ;
     while v1>0 DO
       insert into test_table values (NULL,0,'11','22') ;
       set v1 = v1 - 1 ;
     end while ;
     end ;
     // 
     delimiter;
  

  对比，存储过程耗时，客户端程序耗时

• 触发器

  触发器可以在执行 INsert ，update，delete 运行代码。触发器可以在这些语句之前或之后运行。触发器不会返回结果，可以读取或修改数据。可以使用触发器代替客户端代码强制约束或保证商业逻辑。如可以在类似myisam这样不支持外键存储引擎上模拟外键
  触发器可以简化逻辑提高效率，节约服务器和客户端的数据开销，对非正则化和汇总表很有帮助

  注意：
  对于每个事件，每个表只能有一个触发器，换句话说，不会有两个触发器启动alter insert 事件
  mysql 只支持行级触发器，服务器总是针对每一行进行操作，不是把他们看成一个整体，使得处理大型数据集效率很差
  触发器使服务器正在做的事变得模糊
  触发器很难调试，不利于分析性能
  触发器很引起不明显的死锁和锁等待，如果触发器失败，原始查询也会失败

     create trigger fake_statement_trigger
     before insert on sometable
     for each  row
     begin
             declare v_row_count int default row_count();
             if  v_row_count <> 1 then
                  -- your code here 
             end if;
     end; 
  

• 事件

  一种新型的存储代码：事件，类似定时任务，可以创建事件，她会在特定时间或者时间间隔执行一次预先写好的sql代码。通常将复杂的sql 包装到一个存储过程中，调用一下即可

  事件和连接完全无关，独立运行定时器线程，事件不接受参数，也不返回值，激活了服务器日志，可以看到执行的命令
  适合事件的任务：周期性维护工作、重新建立缓存和汇总以模拟物化视图、或者用于监视和诊断的状态值

     create  event optimize_somedb on schedule every 1 week
     do
     call optimize_tables('somadb')
  

  周期性针对特定的数据库运行一次某个存储过程
  如果周期性事件较长，可能事件会有交叉，mysql 不会对这种行为保护，需要自定义互斥代码，get_lock( )保证每次只有一个事件在执行：

     create event optimize_somsdb on schedules every 1 week
     do
     begin
              declare continue hanlder for sqlexception
                   begin end;
     if get_lock('somedb', 0) then
                        do call optimize_tables('somedb') ;
               end if;
              do release_lock ('somedb') ;
     end
  

  代码中的continue保证了即使存储过程抛出异常，锁也能得到释放
  尽管事件和连接无关，但它和线程有关，服务器有一个主调度线程。必须用配置文件或者命令把它激活
  set GLOBAL event_scheduler : = 1 ;
  一旦激活，每执行一个事件，都会创建一个线程。在事件代码内部，调用 connect_id() 返回一个唯一值，就是线程id。可以通过查看服务器错误日志了解执行情况

• 保存存储代码注释

  触发器家注释的例子：

  create trigger fake_statement_trigger
  before insert on sometable
  for each row
  BEGIN

          declare v_row_count INT DEFAULT row_count();

  /*!9999

    row_count() is 1 except for the first row, so this executes only once per statement.

  */
  IF v_row_count <> 1 THEN

      -- Your code  here

  END IF ;
  END;